﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace HousingDistribution
{
    class HousingQuery
    {
        public DataTable Read(PersonInfo personInfo, string[] streetNo, string housingType, string buildingNo, string doorWay)
        {
            string sql = CreateSql(personInfo, streetNo, housingType, buildingNo, doorWay);

            DataSet houseinfo = DBProc.Instance().ReadData(sql);
            DataTable dt = houseinfo.Tables[0];

            //格式化DT：增加列（显示和选择）
            FormatDataTable(dt);
            
            //处理数据：合并同住一套房子的数据行
            MergeSharingHousing(dt);

            //将现住房始终排在最前面
            TopCurHousingInDT(dt, personInfo.housingInfo);

            return dt;
        }

        public DataTable Read(PersonInfo personInfo)
        {
            string sql = CreateSql(personInfo);

            if (sql == null)
                return null;

            DataSet houseinfo = DBProc.Instance().ReadData(sql);
            DataTable dt = houseinfo.Tables[0];

            //格式化DT：增加列（显示和选择）
            FormatDataTable(dt);

            //处理数据：合并同住一套房子的数据行
            MergeSharingHousing(dt);

            //将现住房始终排在最前面
            TopCurHousingInDT(dt, personInfo.housingInfo);

            return dt;
        }

        private string CreateSql(PersonInfo personInfo)
        {
            //每次选房的时候都需要将该人员已经分到的房子查询出来并首行显示
            if (personInfo.housingInfo != null && personInfo.housingInfo.Count > 0)
            {
                StringBuilder sql = new StringBuilder("select * from housing left join person on person.housingInfo like concat('%#', housing.idhousing, '*%') where 1=1 ");

                sql.Append(AND_STR);

                foreach (HousingOccupancyInfo info in personInfo.housingInfo)
                {
                    sql.Append("idhousing = '");
                    sql.Append(info.id.ToString());
                    sql.Append("'");
                    sql.Append(OR_STR);
                }
                //取出 OR_STR
                sql.Remove(sql.Length - OR_STR.Length, OR_STR.Length);

                sql.Append(")");

                return sql.ToString();
            }
            else
                return null;
        }

        private string CreateSql(PersonInfo personInfo, string[] streetNo, string housingType, string buildingNo, string doorWay)
        {
            StringBuilder sql = new StringBuilder("select * from housing left join person on person.housingInfo like concat('%#', housing.idhousing, '*%') where 1=1 ");

            if (streetNo != null && streetNo.Length > 0)
            {
                StringBuilder streetNoStr = new StringBuilder(AND_STR);

                for (int i = 0; i < streetNo.Length; i++)
                {
                    if (streetNo[i] != null)
                    {
                        streetNoStr.Append("housing.streetNo = '");
                        streetNoStr.Append(streetNo[i]);
                        streetNoStr.Append("'");
                        streetNoStr.Append(OR_STR);
                    }
                }

                //2是最小长度：()
                //大于6 表明至少一个streetNo生效
                //4 为 " or " 字符串的长度
                if (streetNoStr.Length > AND_STR.Length)
                {
                    streetNoStr.Remove(streetNoStr.Length - OR_STR.Length, OR_STR.Length);
                    streetNoStr.Append(")");
                }
                else
                    streetNoStr.Clear();

                sql.Append(streetNoStr.ToString());
            }

            if (housingType != null)
            {
                sql.Append(" and (housing.housingType='");
                sql.Append(housingType);
                sql.Append("')");
            }

            if (buildingNo != null)
            {
                sql.Append(" and (housing.buildingNo='");
                sql.Append(buildingNo);
                sql.Append("')");
            }

            if (doorWay != null)
            {
                sql.Append(" and (housing.doorWay='");
                sql.Append(doorWay);
                sql.Append("')");
            }

            //每次选房的时候都需要将该人员已经分到的房子查询出来并首行显示
            if (personInfo.housingInfo != null && personInfo.housingInfo.Count > 0)
            {
                sql.Append(OR_STR);
                sql.Append("(");
                foreach (HousingOccupancyInfo info in personInfo.housingInfo)
                {
                    sql.Append("idhousing = '");
                    sql.Append(info.id.ToString());
                    sql.Append("'");
                    sql.Append(OR_STR);
                }
                //取出 OR_STR
                sql.Remove(sql.Length - OR_STR.Length, OR_STR.Length);

                sql.Append(")");
            }

            sql.Append(" order by housing.idhousing asc");

            return sql.ToString();
        }

        //合并多人同住的房屋为一行数据
        private void MergeSharingHousing(DataTable dt)
        {
            int idhousingLast = 0;
            int idhousingCur = 0;
            DataRow lastRow = null;
            DataRow curRow = null;

            StringBuilder names = new StringBuilder();
            StringBuilder types = new StringBuilder();
            StringBuilder units = new StringBuilder();

            //增加显示人员类型的列
            dt.Columns.Add(DGV_TYPE_NAME_COL, typeof(System.String));

            for (int k = 1; k < dt.Rows.Count; k++)
            {
                lastRow = dt.Rows[k - 1];
                curRow = dt.Rows[k];

                idhousingLast = Convert.ToInt32(lastRow[HOUSING_ID_COL]);
                idhousingCur = Convert.ToInt32(curRow[HOUSING_ID_COL]);

                //两个id相等，表明该房子多人合住
                if (idhousingCur == idhousingLast)
                {
                    names.Append(lastRow[PERSON_NAME_COL].ToString());
                    names.Append(",");

                    types.Append(DataDictionary.Instance().GetPersonType((PersonTypeEnum)lastRow[PERSON_TYPE_COL]));
                    types.Append(",");

                    units.Append(lastRow[PERSON_UNIT_COL].ToString());
                    units.Append(",");

                    //删除上一行
                    lastRow.Delete();
                }
                //若不相等，则更新idhousingLast的值
                else
                {
                    if (names.Length > 0)
                    {
                        names.Append(lastRow[PERSON_NAME_COL].ToString());
                        lastRow[PERSON_NAME_COL] = names.ToString();

                        names.Clear();
                    }
                    else
                    {
                        if (lastRow[PERSON_NAME_COL] != null && lastRow[PERSON_NAME_COL].ToString().Length > 0)
                        {
                            lastRow[DGV_TYPE_NAME_COL] = DataDictionary.Instance().GetPersonType((PersonTypeEnum)lastRow[PERSON_TYPE_COL]);
                        }
                    }

                    if (types.Length > 0)
                    {
                        types.Append(DataDictionary.Instance().GetPersonType((PersonTypeEnum)lastRow[PERSON_TYPE_COL]));
                        lastRow[DGV_TYPE_NAME_COL] = types.ToString();

                        types.Clear();
                    }

                    if (units.Length > 0)
                    {
                        units.Append(lastRow[PERSON_UNIT_COL].ToString());
                        lastRow[PERSON_UNIT_COL] = units.ToString();

                        units.Clear();
                    }
                }
            }

            //处理最后一行的数据
            if (names.Length > 0)
            {
                names.Append(curRow[PERSON_NAME_COL].ToString());
                curRow[PERSON_NAME_COL] = names.ToString();

                names.Clear();
            }

            if (types.Length > 0)
            {
                types.Append(DataDictionary.Instance().GetPersonType((PersonTypeEnum)curRow[PERSON_TYPE_COL]));
                curRow[DGV_TYPE_NAME_COL] = types.ToString();

                types.Clear();
            }

            if (units.Length > 0)
            {
                units.Append(curRow[PERSON_UNIT_COL].ToString());
                curRow[PERSON_UNIT_COL] = units.ToString();

                units.Clear();
            }

            dt.AcceptChanges();
        }

        private void FormatDataTable(DataTable dt)
        {
            //增加显示元素
            dt.Columns.Add(DGV_SELECT_COL, typeof(System.Boolean));
        }

        //自己的住房始终排在最前
        private void TopCurHousingInDT(DataTable dt, List<HousingOccupancyInfo> housingIds)
        {
            //没有住房，则无需处理住房显示排序
            if (housingIds == null)
                return;

            DataRow curRow = null;

            int index = 0;
            for (int i = 0; i < dt.Rows.Count && index < housingIds.Count; i++)
            {
                curRow = dt.Rows[i];

                curRow[DGV_SELECT_COL] = (this.housingInfoProc.HousingIsCurOccupancy((int)curRow[HOUSING_ID_COL], housingIds)) ? true : false;

                //当前住房则移动到表顶端
                if ((bool)curRow[DGV_SELECT_COL] == true)
                {
                    object[] content = curRow.ItemArray;

                    //交换内容
                    curRow.ItemArray = dt.Rows[index].ItemArray;
                    dt.Rows[index].ItemArray = content;

                    //级数索引+1
                    index++;
                }
                else
                {
                }
            }
        }

        HousingInfoProc housingInfoProc = new HousingInfoProc();

        private string AND_STR = " and (";
        private string OR_STR = " or ";

        String HOUSING_ID_COL = DataDictionary.Instance().HousingTableFieldName(HousingColConsts.ID);
        String PERSON_NAME_COL = DataDictionary.Instance().PersonTableFieldName(PersonColConsts.NAME);
        String PERSON_TYPE_COL = DataDictionary.Instance().PersonTableFieldName(PersonColConsts.TYPE);
        String PERSON_UNIT_COL = DataDictionary.Instance().PersonTableFieldName(PersonColConsts.UNIT);
        String DGV_TYPE_NAME_COL = DataDictionary.Instance().DGVNewColName(DGVNewColConsts.PERSON_TYPE_NAME);
        String DGV_SELECT_COL = DataDictionary.Instance().DGVNewColName(DGVNewColConsts.SELECT);
    }
}
